﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using HZ.Supervise.Model;

namespace HZ.Supervise.DAL
{
    public class DAL_SpecialAuthority
    {
        /// <summary>
        /// 按条件查询医院采购权限
        /// </summary>
        /// <param name="where">sql语句</param>
        /// <param name="currentpage">当前页</param>
        /// <param name="pagesize">每页条数</param>
        /// <param name="totalrecord">总条数</param>
        /// <returns></returns>
        public static DataTable findHopitalAreaSortSourceByWhere(string where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = "select * from View_HopitalAreaSortSource where 1=1" + where + " Order By AreaName";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 禁用该医院的所有药品
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <returns></returns>
        public static int UpdateAllHospitalGoods(HospitalGoods hospitalGoods, int SortSource)
        {
            string sql = "INSERT  [TB_HospitalGoods] ([HHospitalID],[HGoodsId],[Huserid],[Hchangetime])select '" + hospitalGoods.HHospitalID + "',GoodsID,'" + hospitalGoods.Huserid + "',GETDATE() from View_ProcureCatalog where GoodsID not in (select hgoodsid from TB_HospitalGoods where HHospitalID='" + hospitalGoods.HHospitalID + "' and SortSource=" + SortSource + ")";
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
        }
        /// <summary>
        /// 启用该医院的所有的药品
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <returns></returns>
        public static int DeleteAllHospitalGoods(string hospitalId, int SortSource) 
        {
            string sql = "DELETE FROM [TB_HospitalGoods] WHERE HHospitalID='" + hospitalId + "' and HGoodsId in(select GoodsID from View_ProcureCatalog where SortSource=" + SortSource + ")";
            int res = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
            return res;
        }
        /// <summary>
        /// 根据条件查询医院所禁用的药品
        /// </summary>
        /// <param name="HospitalID"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable findHospitalGoodsByWhere(string where,string hospitalId, int currentpage, int pagesize, out int totalrecord) 
        {
            string sql = "select * from View_ProcureCatalog a left join TB_HospitalGoods b on a.ProcureCatalogID=b.HGoodsId where IsUsing=1 " + where + " order by ProductName";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 查询所有日志信息
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static DataTable findAllHospitalGoodsDisableLog(string where) 
        {
            string sql = "select * from [TB_HospitalGoodsDisableLog] where 1=1 "+where;
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            return dt;
        }
        /// <summary>
        /// 插入日志
        /// </summary>
        /// <param name="hospitalGoodsDisableLog"></param>
        /// <returns></returns>
        public static int InsertHospitalGoodsDisableLog(HospitalGoodsDisableLog hospitalGoodsDisableLog)
        {
            int count = 0;
            string sql = "INSERT INTO [TB_HospitalGoodsDisableLog]([Id] ,[BatchRecordID],[InforMation],[ExecutionTime],[GoodsID],[IsDisable],[ExecutionMen])VALUES('" + hospitalGoodsDisableLog.Id + "','" + hospitalGoodsDisableLog.BatchRecordID + "','" + hospitalGoodsDisableLog.InforMation + "',GETDATE(),'"+hospitalGoodsDisableLog.GoodsID+"',"+hospitalGoodsDisableLog.IsDisable+",'"+hospitalGoodsDisableLog.ExecutionMen+"')";
            try
            {
                count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
              
            }
            catch (Exception)
            {
                
                count=-1;
            }
            return count;
            
        }
        /// <summary>
        /// 插入HospitalGoods表
        /// </summary>
        /// <param name="hospotalGoodList"></param>
        /// <returns></returns>
        public static int InsertHospitalGoods(IList<HospitalGoods> hospotalGoodList) 
        {
            int count = 0;
            StringBuilder strsql = new StringBuilder();
            for (int i = 0; i < hospotalGoodList.Count; i++)
            {
                strsql.Append("INSERT INTO [TB_HospitalGoods] ([HHospitalID],[HGoodsId],[Huserid],[Hchangetime])VALUES('" + hospotalGoodList[i].HHospitalID + "','" + hospotalGoodList[i].HGoodsId + "','" + hospotalGoodList[i].Huserid + "',GETDATE()) "); 
            }
            try
            {
                count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(strsql.ToString());
                return count;
            }
            catch (Exception)
            {
                
                return -1;
            }

        }
        /// <summary>
        /// 插入日志表
        /// </summary>
        /// <param name="hospitalGoodsLog"></param>
        /// <returns></returns>
        public static int InsertHospitalGoodsLog(HospitalGoodsLog hospitalGoodsLog) 
        {
            int count=0;
            string sql = "INSERT INTO [TB_HospitalGoodsLog] ([Id],[HHospitalID],[HGoods],[Hchangetime],[HuserId])VALUES('" + hospitalGoodsLog.Id + "','" + hospitalGoodsLog.HHospitalID + "','" + hospitalGoodsLog.HGoods + "',GETDATE(),'"+hospitalGoodsLog.HuserId+"')";
            try
            {
                count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql.ToString());
                return count;
            }
            catch (Exception)
            {

                return -1;
            }
        
        }
        /// <summary>
        /// 根据医院Id查询日志表
        /// </summary>
        /// <param name="hospitalId"></param>
        /// <returns></returns>
        public static DataTable GetAllHospitalLog(string hospitalId) 
        {
            string sql = "select * from TB_HospitalGoodsLog where HHospitalID='"+hospitalId+"'";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            return dt;
        }
        /// <summary>
        /// 根据hospitalList删除
        /// </summary>
        /// <param name="hospotalGoodList"></param>
        /// <returns></returns>
        public static int DeleteHospitalGoods(IList<HospitalGoods> hospotalGoodList) 
        {
            int count = 0;
            StringBuilder strsql = new StringBuilder();
            for (int i = 0; i < hospotalGoodList.Count; i++)
            {
                strsql.Append("DELETE FROM [TB_HospitalGoods] where HHospitalID='"+hospotalGoodList[i].HHospitalID+"' and HGoodsId='"+hospotalGoodList[i].HGoodsId+"'");
            }
            try
            {
                count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(strsql.ToString());
                return count;
            }
            catch (Exception)
            {

                return -1;
            }
        }
        /// <summary>
        /// 查询药品撤废目录
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable findTAreaDeleteProcure(string where, int currentpage, int pagesize, out int totalrecord)
        {
            string sql = "select * from dbo.View_AreaDeleteProcure where 1=1 " + where + " Order By DProcureCatalogID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 查询采购目录,关联被禁用的
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable findHospitalGoodsDisable(string where, int currentpage, int pagesize, out int totalrecord)
        {

            string sql = "select a.*,b.BatchRecordID from View_ProcureCatalog as a right join TB_HospitalGoodsDisable as b on a.GoodsID=b.GoodsID where a.IsUsing=1" + where + " order by GoodsID";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 查询出所有禁用的药品批号
        /// </summary>
        /// <returns></returns>
        public static DataTable findAllHospitalGoodsDisable() 
        {
            string sql = "select * from TB_HospitalGoodsDisable";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(sql);
            return dt;
        }
        /// <summary>
        ///根据批号，流水号删除已禁用的数据
        /// </summary>
        /// <param name="hospitalGoodsDisableList"></param>
        /// <returns></returns>
        public static int DeleteHospitalGoodsDisable(IList<HospitalGoodsDisable> hospitalGoodsDisableList) 
        {
            int count = 0;
            StringBuilder strsql = new StringBuilder();
            for (int i = 0; i < hospitalGoodsDisableList.Count; i++)
            {
                strsql.Append("DELETE FROM [TB_HospitalGoodsDisable] where BatchRecordID='" + hospitalGoodsDisableList[i].BatchRecordID + "' and GoodsID='"+hospitalGoodsDisableList[i].GoodsID+"'");
            }
            try
            {
                count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(strsql.ToString());
                return count;
            }
            catch (Exception)
            {

                return -1;
            }
        }
      

        /// <summary>
        /// 新增药品批号禁用
        /// </summary>
        /// <param name="hospitalGoodsDisableList"></param>
        /// <returns></returns>
        public static int InsertHospitalGoodsDisable(IList<HospitalGoodsDisable> hospitalGoodsDisableList) 
        {
            int count = 0;
            StringBuilder strsql = new StringBuilder();
            for (int i = 0; i < hospitalGoodsDisableList.Count; i++)
            {
                strsql.Append("INSERT INTO [TB_HospitalGoodsDisable]([BatchRecordID],[GoodsID] ,[ExecutionTime],[ExecutionMen]) VALUES('" + hospitalGoodsDisableList[i].BatchRecordID + "','" + hospitalGoodsDisableList[i].GoodsID + "',GETDATE(),'"+hospitalGoodsDisableList[i].ExecutionMen+"')");
            }
            try
            {
                count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(strsql.ToString());
                return count;
            }
            catch (Exception)
            {

                return -1;
            }
        }
        /// <summary>
        /// 查询采购目录
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable findProcureCatalog(string where, int currentpage, int pagesize, out int totalrecord)
        {

            string sql = "select * from dbo.View_ProcureCatalog where IsUsing =1 " + where + " Order By GoodsID ";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 查询调价目录
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static DataTable AreaPrice(string where, int currentpage, int pagesize, out int totalrecord)
        {

            string sql = "select * from dbo.View_AreaPrice where 1=1" + where + " Order By Areaid ";
            HZ.Data.PageDescribe des = new HZ.Data.PageDescribe(sql, pagesize);
            des.CurrentPage = currentpage;
            totalrecord = des.TotalCount;
            return des.GetSpecailPage();
        }

        /// <summary>
        /// 添加调价
        /// </summary>
        /// <param name="areaID"></param>
        /// <param name="goodsID"></param>
        /// <param name="Aprice"></param>
        /// <param name="type"></param>
        /// <param name="Aadduserid"></param>
        /// <returns></returns>
       public static int InsertAreaPrice(string areaID, string goodsID, string Aprice, Boolean type, string Aadduserid)
        {
            int count = 0;
            StringBuilder sql = new StringBuilder();
            if (type)
            {
                sql.Append(string.Format("insert TB_AreaPrice values('{0}','{1}',1,{2},getdate(),'{3}')", areaID, goodsID, Aprice, Aadduserid));
              
            }
            else
            {
                string[] strAreaId = areaID.Split(',');
               
                foreach (var item in strAreaId)
                {
                    sql.Append(string.Format("insert TB_AreaPrice values('{0}','{1}',0,{2},getdate(),'{3}')", item, goodsID, Aprice, Aadduserid));

                }
               
            }

            try
            {
                count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql.ToString());
                return count;
            }
            catch (Exception)
            {

                return -1;
            }
        }

        /// <summary>
        /// 修改调价
        /// </summary>
        /// <param name="areaID"></param>
        /// <param name="goodsID"></param>
        /// <param name="Aprice"></param>
        /// <param name="type"></param>
        /// <param name="Aadduserid"></param>
        /// <returns></returns>
       public static int UpdataAreaPrice(string areaID, string goodsID, string Aprice,Boolean type,string Aadduserid)
       {
           int count = 0;
           StringBuilder sql = new StringBuilder();
           HZ.Data.DbHelperFactory.Create().ExecuteNonQuery("delete TB_AreaPrice where ProcureCatalogID = '" + goodsID + "'");
           if (type)
           {
               sql.Append(string.Format("insert TB_AreaPrice values('{0}','{1}',1,{2},getdate(),'{3}')", areaID, goodsID, Aprice, Aadduserid));
             

           }
           else
           {
               string[] strAreaId = areaID.Split(',');
              
               foreach (var item in strAreaId)
               {
                   sql.Append(string.Format("insert TB_AreaPrice values('{0}','{1}',0,{2},getdate(),'{3}')", item, goodsID, Aprice, Aadduserid));
                  
               }
               
           }

           try
           {
               count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql.ToString());
               return count;
           }
           catch (Exception)
           {

               return -1;
           }
       }
      /// <summary>
      /// 添加撤废
      /// </summary>
      /// <param name="areaID"></param>
      /// <param name="goodsID"></param>
      /// <param name="type"></param>
      /// <param name="Aadduserid"></param>
      /// <returns></returns>
       public static int InsertAreaDeleteProcure(string areaID, string goodsID,Boolean type, string Aadduserid)
       {
           int count = 0;
           StringBuilder sql = new StringBuilder();
           if (type)
           {
               sql.Append(string.Format("insert TB_AreaDeleteProcure values('{0}','{1}',1,getdate(),'{2}')", areaID, goodsID, Aadduserid));

           }
           else
           {
               string[] strAreaId = areaID.Split(',');
               
               foreach (var item in strAreaId)
               {
                    sql.Append(string.Format("insert TB_AreaDeleteProcure values('{0}','{1}',0,getdate(),'{2}')", item, goodsID, Aadduserid));
               }
              
           }
           try
           {
               count = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql.ToString());
               return count;
           }
           catch (Exception)
           {
               
                return -1;
           }
       }


       /// <summary>
       /// 更新医院采购权限
       /// </summary>
       /// <param name="Lhas"></param>
       /// <returns></returns>
       public static int UpdateHopitalAreaSortSource(List<HZ.Supervise.Model.View.HopitalAreaSortSource> Lhas)
       {
           StringBuilder sb = new StringBuilder();
           foreach (HZ.Supervise.Model.View.HopitalAreaSortSource ff in Lhas)
           {
               sb.Append("update TB_HopitalSortSource set HIsUsering='" + ff.IsUsering + "',Hchangetime='" + ff.Hchangetime + "',Huserid='" + ff.Huserid + "' where Hid='" + ff.Hid + "' \n");
               sb.Append("insert TB_AreaPriceHospitalLoge( Ltableid, Ladduserid, Lcontent,Lsort)  values('" + ff.Hid + "','" + ff.Huserid + "','" + Fun2.SoapSerializer(ff) + "','0') \n");

           }
           int i = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sb.ToString());
           return i;
       }

        /// <summary>
        /// 获取历史修改记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id1">表主键</param>
        /// <param name="id2">历史： 0 医院采购范围，1地区采购药品 ，2地区采购价格</param>
        /// <returns></returns>
        public static List<T> GetHostory<T>(string id1, string id2,string id3,string id4) where T : new()
        {
            string strwhere = "";
            if (!id1.Equals("")) strwhere += " and Ltableid='" + id1 + "'";
            if (!id2.Equals("")) strwhere += " and  Lsort='" + id2 + "'";
            if (!id3.Equals("")) strwhere += " and Lareaid='" + id3 + "'";
            if (!id4.Equals("")) strwhere += " and Lgoodsid='" + id4 + "'";
            DataTable dt;
            string strselect =string.Format("select Lcontent from TB_AreaPriceHospitalLoge where 1=1{0} ",strwhere);
            dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);

            List<T> Lhas = new List<T>();
            foreach (DataRow dw in dt.Rows)
            {
                T has = new T();
                has = (T)Fun2.Deserialize(dw[0].ToString());
                Lhas.Add(has);
            }
            return Lhas;

        }

        /// <summary>
        /// 获取流水号在该用户地区下的价格和撤废情况
        /// </summary>
        /// <param name="ProcureCatalogid"></param>
        /// <param name="Areaid"></param>
        /// <returns></returns>
        public static DataTable GetProcureCatalogAllstate(string ProcureCatalogid, string Areaid)
        {
            string strareaid="";
            if (Areaid.Substring(4, 2) == "00")  //如果登录用户是市级单位则显示该市全区，如果是区单位就显示该区
            {
                strareaid = Areaid.Substring(0, 4);
            }
            else
            {
                strareaid = Areaid;
            }
            string strselect = "select aa.AreaID,AreaName,( case when bb.ProcureCatalogID is null then cc.DProcureCatalogID else bb.ProcureCatalogID end ) ProcureCatalogID, bb.Aprice,CONVERT(bit,case when  cc.DID is null then 1 else 0 end) DID from (select *from TB_Area  where AreaID like '" + strareaid + "%' )aa left outer join (select * from TB_AreaPrice where ProcureCatalogID ='" + ProcureCatalogid + "')bb on aa.AreaID=bb.Areaid  left outer join(select * from TB_AreaDeleteProcure where DProcureCatalogID='" + ProcureCatalogid + "')cc on aa.AreaID=cc.DAreaid order by AreaID";
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(strselect);
            return dt;
        }
        /// <summary>
        ///更调价和地区控制
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int UpdateChangeall(string sql)
        {
            int i = HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
            return i;
        }
    }
}
